﻿using System;
using System.Collections.Generic;

using System.Text;
using Estimate.Common.DBFieldTypes;
using Estimate.Common.Entity;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 对CityFore, SiteFore,ZytCityFore,RainFore表进行操作的基础类
    /// 由于几张表的表结构完全一致，所以使用基础类进行处理
    /// 
    /// </summary>
    public class ForeRepository : BaseRepository
    {

        public ForeRepository(string connectionString, string tableName) :base(connectionString,tableName)
        {
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}](id bigint identity(1,1), [date] char(8) not null, clock varchar(8) not null, span varchar(9) not null, sendpoint varchar(8) not null, type varchar(8) not null, semitype varchar(8) not null, [value] text, semi text ) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (date,span,type,semitype,clock,sendpoint) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            return sqlcreate;
        }

        /// <summary>
        /// 更新预处理结果
        /// 如果用户改变了 semitype, 则需要新建一条记录
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public void UpdateSemiAndValue( ForeEntity foreEntity )
        {
            string sql = String.Format("UPDATE [{0}] SET semi=@semi, value=@value WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype and  clock=@clock and sendpoint=@sendpoint ", _tableName);
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@date", foreEntity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", foreEntity.Clock.ToString());
            param[2] = new SqlParameter("@span", foreEntity.Span.ToString());
            param[3] = new SqlParameter("@type", foreEntity.Type.ToString());
            param[4] = new SqlParameter("@semitype", foreEntity.Semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", foreEntity.Sendpoint.ToString());
            param[6] = new SqlParameter("@semi", foreEntity.Semi);
            param[7] = new SqlParameter("@value", foreEntity.Value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 新增一条记录
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public void InsertForeRecord(ForeEntity foreEntity)
        {
            string sql = String.Format("INSERT INTO [{0}] (date,span,type,semitype,clock,sendpoint,value,semi) values(@date ,@span ,@type ,@semitype ,@clock,@sendpoint ,@value,@semi)", _tableName);
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@date", foreEntity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", foreEntity.Clock.ToString());
            param[2] = new SqlParameter("@span", foreEntity.Span.ToString());
            param[3] = new SqlParameter("@type", foreEntity.Type.ToString());
            param[4] = new SqlParameter("@semitype", foreEntity.Semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", foreEntity.Sendpoint.ToString());
            param[6] = new SqlParameter("@semi", foreEntity.Semi);
            param[7] = new SqlParameter("@value", foreEntity.Value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity GetForeRecord(DateTime date, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, ForeFactorType type, ForeSemiType semitype)
        {
            string sql = String.Format("SELECT id,value,semi FROM [{0}] WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype  and clock=@clock and  sendpoint=@sendpoint", _tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", clock.ToString());
            param[2] = new SqlParameter("@span", span.ToString());
            param[3] = new SqlParameter("@type", type.ToString());
            param[4] = new SqlParameter("@semitype", semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql,param);
            if(reader.Read())
            {
                int id = Convert.ToInt32(reader["id"].ToString());
                string value =  reader["value"].ToString();
                string semi = reader["semi"].ToString();
                connection.Close();
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                return entity;                
            }
            return null;
        }

        /// <summary>
        /// 设置一条记录，如果不存在，就会新增。否则会更新
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public virtual void SetForeRecord(ForeEntity foreEntity)
        {
            if (this.IsForeRecordExist(foreEntity.Date,foreEntity.Clock,foreEntity.Span,foreEntity.Sendpoint,foreEntity.Type, foreEntity.Semitype))
            {
                this.UpdateSemiAndValue(foreEntity);
            }
            else
            {
                this.InsertForeRecord(foreEntity);
            }
        }

        /// <summary>
        /// 检查数据库中是否已经存在一条记录。
        /// 判断条件就是 date,clock,span,type,semitype,sendpoint 这几个字段
        /// </summary>
        /// <param name="foreEntity"></param>
        /// <returns></returns>
        public virtual bool IsForeRecordExist(DateTime date, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, ForeFactorType type, ForeSemiType semitype)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype  and clock=@clock  and  sendpoint=@sendpoint", _tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", clock.ToString());
            param[2] = new SqlParameter("@span", span.ToString());
            param[3] = new SqlParameter("@type", type.ToString());
            param[4] = new SqlParameter("@semitype", semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        /// <summary>
        /// 使用站号列表筛选semi
        /// </summary>
        /// <param name="semis">semi字符串数组</param>
        /// <param name="deststream">站点列表字符串</param>
        /// <returns>相关站点的semi字符串</returns>
        public virtual string FilterSemiWithStationIds(string[] semis, string deststream)
        {
            List<string> result = new List<string>();
            foreach (string semi in semis)
            {
                //无论是temp还是water,|分隔，其每个小semi前5位都是站号
                string[] ss = Regex.Split(semi, @"\|", RegexOptions.IgnoreCase);
                foreach (string s in ss)
                {
                    if (s.Length < 6)
                        continue;
                    if (deststream.IndexOf(s.Substring(0, 5)) != -1)
                        result.Add(s);
                }
            }
            return string.Join("|", result.ToArray());
        }


        /// <summary>
        /// 使用站号列表筛选semi
        /// </summary>
        /// <param name="semi">semi字符串</param>
        /// <param name="deststream">站点列表字符串</param>
        /// <returns>相关站点的semi字符串</returns>
        public virtual string FilterSemiWithStationIds(string semi, string deststream)
        {
            List<string> result = new List<string>();

            //如果涉及到站点信息，镇江在前端显示中使用的是 58252, 在 semi 中实际上是58248
            if( deststream.IndexOf("58252") != -1)
                deststream = deststream.Replace("58252","58248");

            //无论是temp还是water,|分隔，其每个小semi前5位都是站号
            string[] ss = Regex.Split(semi, @"\|", RegexOptions.IgnoreCase);
            foreach (string s in ss)
            {
                string station = s;

                if (s.Length < 6)
                    continue;
                if (deststream.IndexOf(station.Substring(0, 5)) != -1)
                    result.Add(s);
            }
            return string.Join("|", result.ToArray());
        }

        public virtual void FilterSemiWithStationIds(ForeEntity semi, string deststream)
        {
            //List<string> result = new List<string>();

            //如果涉及到站点信息，镇江在前端显示中使用的是 58252, 在 semi 中实际上是58248
            if (deststream.Contains("58252"))
                deststream = deststream.Replace("58252", "58248");

            //无论是temp还是water,|分隔，其每个小semi前5位都是站号
            string[] ss = semi.Semi.Split(new char[] { '|' });// Regex.Split(semi, @"\|", RegexOptions.IgnoreCase);
            foreach (string s in ss)
            {
                string station = s;

                if (s.Length < 6)
                    continue;
                if (deststream.Contains(station.Substring(0, 5)))
                    semi.semis.Add(s);
            }

        }

        /// <summary>
        /// 查询某天的一个预报
        /// </summary>
        /// <param name="destStream">指定的站点</param>
        /// <returns></returns>
        public virtual string InquirySemi(string destStream, DateTime date, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, ForeFactorType type, ForeSemiType semitype)
        {
            string forestr = GetForeRecord(date,clock,span,sendpoint,type,semitype).Semi;
            return FilterSemiWithStationIds(new string[] { forestr }, destStream);            
        }

        /// <summary>
        /// 给定日期范围及查询条件，获取相应的所有semi值
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        public virtual ForeEntity[] InquerySemiWithDateRange(DateTime start, DateTime end, ForeFactorType type, ForeSemiType semitype)
        {
            List<ForeEntity> result = new List<ForeEntity>();

            string sql = String.Format("SELECT * FROM [{0}] WHERE date>='{1}' and date<='{2}' and type='{3}' and  semitype='{4}'",
                                        new object[] { _tableName, 
                                        start.Date.ToString("yyyyMMdd"), 
                                        end.Date.ToString("yyyyMMdd"),
                                        type.ToString(),
                                        semitype.ToString()
                                       });


            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
            while (reader.Read())
            {
                string value = reader["value"].ToString();
                string semi = reader["semi"].ToString();
                DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                result.Add(entity);
            }
            connection.Close();
            return result.ToArray();

        }      

        /// <summary>
        /// 给定日期范围及查询条件，获取相应的所有semi值
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        public virtual ForeEntity[] InquerySemiWithDateRange(DateTime start, DateTime end, ForeClockType[] clocks, ForeSpanType span, ForeSendpointType[] sendpoints, ForeFactorType type, ForeSemiType semitype, bool needCache)
        {
            //引入 memcached 缓存机制
            //List<object> ps = new List<object>() { start, end,span, type, semitype, "InquerySemiWithDateRange1", this._tableName };
            //foreach (ForeClockType clock in clocks)
            //{
            //    ps.Add(clock);
            //}
            //foreach (ForeSendpointType sendpoint in sendpoints)
            //{
            //    ps.Add(sendpoint);
            //}

            //string cacheKey = getValideCacheKey(ps);
            //object cache = CacheClient.Get(cacheKey);

            //if (needCache == false || cache == null)
            //{                 
                List<ForeEntity> result = new List<ForeEntity>();

                //将clock转化为string
                string[] strClock = new string[clocks.Length];
                for (int i = 0; i < clocks.Length; i++)
                {
                    strClock[i] = clocks[i].ToString();
                }
                string[] strSendpoint = new string[sendpoints.Length];
                for (int i = 0; i < sendpoints.Length; i++)
                {
                    strSendpoint[i] = sendpoints[i].ToString();
                }
                string sql = String.Format("SELECT * FROM [{0}] WHERE date>='{1}' and date<='{2}' and span='{3}'  and type='{4}' and  semitype='{5}'  and clock in ({6}) and  sendpoint in ({7})",
                    new object[] { _tableName, 
                    start.Date.ToString("yyyyMMdd"), 
                    end.Date.ToString("yyyyMMdd"),
                    
                    span.ToString(),
                    type.ToString(),
                     semitype.ToString(),
                     string.Format("'{0}'", string.Join("','", strClock)),
                      string.Format("'{0}'", string.Join("','", strSendpoint))
                     
                    });


                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi = reader["semi"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                    result.Add(entity);
                }
                connection.Close();
                //if (needCache)
                //    CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            //}
            //else
            //{
            //    ForeEntity[] result = cache as ForeEntity[];
            //    return result;
            //}
            
        }

        public virtual ForeEntity[] InquerySemiWithDateRange(DateTime start, DateTime end, ForeClockType[] clocks, ForeSpanType[] spans, ForeSemiType semitype)
        {
            List<ForeEntity> result = new List<ForeEntity>();

            //将clock转化为string
            string[] strClock = new string[clocks.Length];
            for (int i = 0; i < clocks.Length; i++)
                strClock[i] = clocks[i].ToString();

            //string[] strSendpoint = new string[sendpoints.Length];
            //for (int i = 0; i < sendpoints.Length; i++)
            //    strSendpoint[i] = sendpoints[i].ToString();

            string[] strSpan = new string[spans.Length];
            for (int i = 0; i < spans.Length; i++)
                strSpan[i] = spans[i].ToString();


            string sql = String.Format("SELECT * FROM [{0}] WHERE date>='{1}' and date<='{2}' and span in'{3}' and  semitype='{4}'  and clock in ({5})",
                                         new object[] { _tableName, 
                                         start.Date.ToString("yyyyMMdd"), 
                                         end.Date.ToString("yyyyMMdd"),
                                         string.Format("'{0}'", string.Join("','", strSpan)),
                                         semitype.ToString(),
                                         string.Format("'{0}'", string.Join("','", strClock))
                     
                                        });


            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
            while (reader.Read())
            {
                string value = reader["value"].ToString();
                string semi = reader["semi"].ToString();
                DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                //ForeSemiType semitype = (ForeSemiType)Enum.Parse(typeof(ForeSemiType), reader["ForeSemiType"].ToString());
                ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                result.Add(entity);
            }
            connection.Close();

            return result.ToArray();


        }   
       

        /// <summary>
        /// 当有了实况值后，会根据筛选出 date, clock, span 三个参数。符合这三个参数的记录，是与实况记录相吻合的预报记录
        /// 获取这些预报记录，然后将计算semi
        /// </summary>
        /// <returns>符合条件的预报记录</returns>
        public virtual ForeEntity[] RealValueFilteForeRecord(DateTime date, ForeClockType clock, ForeSpanType span, ForeFactorType type, ForeSemiType semitype, bool needCache)
        {
            List<object> ps = new List<object>() { date, clock, span, type, semitype ,this._tableName , "RealValueFilteForeRecord"};
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (needCache == false || cache == null)
            {
                string sql = String.Format("SELECT * FROM [{0}] WHERE date=@date and span=@span  and type=@type and  semitype=@semitype and clock=@clock ", _tableName);
                SqlParameter[] param = new SqlParameter[5];
                param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
                param[1] = new SqlParameter("@clock", clock.ToString());
                param[2] = new SqlParameter("@span", span.ToString());
                param[3] = new SqlParameter("@type", type.ToString());
                param[4] = new SqlParameter("@semitype", semitype.ToString());
                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);
                List<ForeEntity> result = new List<ForeEntity>();
                while (reader.Read())
                {
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    string value = reader["value"].ToString();
                    string semi = reader["semi"].ToString();
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                    result.Add(entity);
                }
                connection.Close();
                if( needCache)
                    CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ForeEntity[] result = cache as ForeEntity[];
                return result;
            }
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity[] GetForeRecordWithDateRange(DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSpanType[] spans, ForeSendpointType[] sendpoints, ForeFactorType[] types, ForeSemiType semitype, bool needCache)
        {
            //引入 memcached 缓存机制
            List<object> ps = new List<object>() { beginDate, endDate, semitype, this._tableName , "GetForeRecordWithDateRange2" };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            foreach (ForeSpanType span in spans)
            {
                ps.Add(span);
            }
            foreach (ForeFactorType type in types)
            {
                ps.Add(type);
            }
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (needCache == false || cache == null)
            {
                string sql = String.Format("SELECT * FROM [{0}] WHERE date >= '{1}' and date <='{2}'  and span in ({3})  and type in ({4}) and  semitype='{5}'  and clock in ({6}) and  sendpoint in ({7})",
                    new object[]{
                    _tableName,
                    beginDate.ToString("yyyyMMdd"),endDate.ToString("yyyyMMdd"),
                    this.makeSpanDBIn(spans),
                    this.makeFactorDBIn(types),
                    semitype.ToString(),
                    this.makeClocksDBIn( clocks), this.makeSendpointsDBIn( sendpoints)
                });
                List<ForeEntity> result = new List<ForeEntity>();
                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi = reader["semi"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                    result.Add(entity);
                }
                connection.Close();
                if (needCache)
                    CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();

            }
            else
            {
                ForeEntity[] result = cache as ForeEntity[];
                return result;
            }
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity[] GetForeRecordWithDate(DateTime select_date, ForeClockType[] clocks, ForeSendpointType[] sendpoints, ForeFactorType factor, ForeSemiType semitype, bool needCache)
        {
            //引入 memcached 缓存机制
            List<object> ps = new List<object>() { select_date, factor, semitype,this._tableName , "GetForeRecordWithDate" };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            string cacheKey = getValideCacheKey(ps);
            object cache = CacheClient.Get(cacheKey);

            if (needCache == false || cache == null)
            {
                string sql = String.Format("SELECT * FROM [{0}] WHERE date = '{1}'  and span <> ''  and type = '{2}' and  semitype='{3}'  and clock in ({4}) and  sendpoint in ({5}) ORDER BY sendpoint, span",
                    new object[]{
                    _tableName,
                    select_date.ToString("yyyyMMdd"),
                    factor.ToString(),
                    semitype.ToString(),
                    this.makeClocksDBIn( clocks),
                    this.makeSendpointsDBIn( sendpoints)
                });
                List<ForeEntity> result = new List<ForeEntity>();
                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi = reader["semi"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                    result.Add(entity);
                }
                connection.Close();
                if (needCache)
                    CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ForeEntity[] result = cache as ForeEntity[];
                return result;
            }
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity[] GetForeRecordWithDateRange(DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSendpointType[] sendpoints, ForeSemiType semitype, bool needCache)
        {
            //引入 memcached 缓存机制
            List<object> ps = new List<object>() { beginDate, endDate,semitype,this._tableName , "GetForeRecordWithDateRange3" };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            string cacheKey = getValideCacheKey(ps);

            object cache = CacheClient.Get(cacheKey);

            if (needCache == false || cache == null)
            {

                string sql = String.Format("SELECT * FROM [{0}] WHERE date >= '{1}' and date <='{2}'  and span <> ''  and type <> '' and  semitype='{3}'  and clock in ({4}) and  sendpoint in ({5}) ORDER BY date",
                    new object[]{
                    _tableName,
                    beginDate.ToString("yyyyMMdd"),
                    endDate.ToString("yyyyMMdd"),
                    semitype.ToString(),
                    this.makeClocksDBIn( clocks), this.makeSendpointsDBIn( sendpoints)
                });
                List<ForeEntity> result = new List<ForeEntity>();
                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi = reader["semi"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi);
                    result.Add(entity);
                }
                connection.Close();
                if (needCache)
                    CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                ForeEntity[] result = cache as ForeEntity[];
                return result;
            }
        }

        public void DeleteRecordWithDateRange(DateTime begin, DateTime end)     
        {
            string sql = String.Format("DELETE FROM [{0}] WHERE date >= '{1}' and date <='{2}'",
               new object[]{
                    _tableName,
                    begin.ToString("yyyyMMdd"),
                    end.ToString("yyyyMMdd")
                });
            DBHelper.ExecuteSql(_connectionString, sql);
        }

      
    }
}
